package com.dullong.firedocmanager.service;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

import com.dullong.firedocmanager.bean.User;
import com.dullong.firedocmanager.service.base.BaseServiceSupport;
import com.dullong.firedocmanager.util.ClassRefUtil;
import com.dullong.firedocmanager.util.ConnectionUtil;

@Service
public class UserServiceSupport implements UserService {

	public UserServiceSupport() {
	}

	@Override
	public User getUserById(String userId) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			return runner.query(conn, "select * from user where userName=?",
					new BeanHandler<User>(User.class), userId);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}

		return null;
	}

	@Override
	public User getUserByEmail(String email) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			return runner.query(conn, "select * from user where email=?",
					new BeanHandler<User>(User.class), email);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}

		return null;
	}

	@Override
	public String save(User user) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		if (user == null || null == user.getUserName()
				|| user.getUserName().equals("")) {
			return null;
		}
		User u = getUserById(user.getUserName());
		if (null == u) {
			try {
				Map<String, String> fieldValueMap = ClassRefUtil
						.getFieldValueMap(user);
				int size = fieldValueMap.size();
				Object[] obj = new Object[size];
				String[] values = new String[size];
				Object[] array = fieldValueMap.keySet().toArray();
				for (int i = 0; i < size; i++) {
					obj[i] = fieldValueMap.get(array[i]);
					values[i] = "?";
				}
				String insertString = StringUtils.join(array, ",");
				String valuesString = StringUtils.join(values, ",");
				runner.update(conn, "insert into user(" + insertString
						+ ") values(" + valuesString + ")", obj);
				return user.getUserName();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				DbUtils.closeQuietly(conn);
			}
		}

		return null;
	}

	@Override
	public String update(String sql, Object... obj) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();

		try {
			runner.update(conn, sql, obj);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "更新失败！" + e.getMessage();
		} finally {
			DbUtils.closeQuietly(conn);
		}

		return "更新成功！";

	}

	@Override
	public List<User> getAllUsers() {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();

		try {
			return runner.query(conn, "select * from user ",
					new BeanListHandler<User>(User.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}

		return null;
	}

	@Override
	public String save(User user, Integer... role) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		if (user == null || null == user.getUserName()
				|| user.getUserName().equals("")) {
			return null;
		}
		User u = getUserById(user.getUserName());
		if (null == u) {
			try {
				Map<String, String> fieldValueMap = ClassRefUtil
						.getFieldValueMap(user);
				int size = fieldValueMap.size();
				Object[] obj = new Object[size];
				String[] values = new String[size];
				Object[] array = fieldValueMap.keySet().toArray();
				for (int i = 0; i < size; i++) {
					obj[i] = fieldValueMap.get(array[i]);
					values[i] = "?";
				}
				String insertString = StringUtils.join(array, ",");
				String valuesString = StringUtils.join(values, ",");
				int update = runner.update(conn, "insert into user("
						+ insertString + ") values(" + valuesString + ")", obj);
				if (update == 1 && role != null) {
					for (Integer in : role)
						runner.update(
								conn,
								"replace into authority(username,roleid) values(?,?) ",
								user.getUserName(), in);
					updateUserAuthorityDes(user.getUserName());
				}
				return user.getUserName();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				DbUtils.closeQuietly(conn);
			}
		}

		return null;
	}

	@Override
	public User getUserByUsername(String username) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			return runner.query(conn, "select * from user where userName=?",
					new BeanHandler<User>(User.class), username);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}

		return null;
	}

	@Override
	public boolean isExist(String username, String email) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			User u = runner.query(conn,
					"select * from user where userName=? or email=?",
					new BeanHandler<User>(User.class), username, email);
			if (u != null) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return false;
	}

	@Override
	public User getUserByUP(String username, String psw) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			return runner.query(conn,
					"select * from user where userName=? and psw=?",
					new BeanHandler<User>(User.class), username, psw);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}

		return null;
	}

	@Override
	public boolean delete(String username) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			int i = 0;
			i += runner.update(conn, "delete from user where username=?",
					username);
			i += runner.update(conn, "delete from authority where username=?",
					username);
			if (i > 0) {
				return true;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return false;
	}

	@Override
	public boolean addRole(String username, Integer role) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			int i = 0;
			i += runner.update(conn,
					"replace into authority(username,roleid) values(?,?)",
					username, role);
			updateUserAuthorityDes(username);
			if (i > 0) {
				return true;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return false;
	}

	@Override
	public boolean removeRole(String username, Integer role) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			int i = 0;
			i += runner.update(conn,
					"delete from authority where username=? and roleid=?",
					username, role);
			updateUserAuthorityDes(username);
			if (i > 0) {
				return true;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}

		return false;
	}

	private void updateUserAuthorityDes(String username) {
		Connection conn = ConnectionUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			List<String> query = runner
					.query(conn,
							"SELECT role.title FROM role LEFT JOIN authority on role.id=authority.roleid where authority.username=?",
							new ColumnListHandler<String>(1), username);
			String join = StringUtils.join(query, ",");
			runner.update(conn, "update user set quanxian=? where username=?",
					join, username);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}

	}
}
